<?php
  set_time_limit(1200); // 如果大文件,可能需要一定耗时
  
  define('WORD_ROOT_PATH', dirname(__FILE__));
  include_once( WORD_ROOT_PATH . "/reader.php");  
	include_once( WORD_ROOT_PATH . "/db/DBMySQL.php");  
	
	$t = $_POST['t'];
	if(empty($t)) {
		$t = "0";
	}
	
	if("2" == $t) {
		$typeName = "iBT";
	} else if("1" == $t) {
		$typeName = "GRE";
	} else {
		$typeName = "SAT";		
	}		
	
  
	// 对上传文件的处理	
	$fileName = $_FILES["userfile"]["name"];
  if(!empty($fileName)) {
    
    $tmpName = $_FILES["userfile"]["tmp_name"];
    
    $error = $_FILES["userfile"]["error"];
    if($error > 0) { // 上传失败
      echo "文件上传发生错误,请回退重新上传";
      exit();
    }
    
    $fileSplit = explode(".", $fileName);    
    $nSize = count($fileSplit);
    if(2 > $nSize) { // 没有文件后缀名?
      echo "上传的文件'$fileName'不是合法的excel文件，请回退上传正确的excel文件";
      exit();
    }
    $suffix = $fileSplit[$nSize-1];
    if("xls" != strtolower($suffix)) { // 文件后缀名不是xls
      echo "上传的文件'$fileName'不是合法的excel文件，请回退上传正确的excel文件(*.xls)";
      exit();      
    }    
    
    // ExcelFile($filename, $encoding); 
    $data = new Spreadsheet_Excel_Reader(); 
    
    // Set output Encoding. 
    $data->setOutputEncoding('UTF-8');
    
    //”data.xls”是指要导入到mysql中的excel文件
    $data->read($tmpName);
    
  }  
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>导入<?=$typeName?>单词表格结果</title>
<style type="text/css">
*{
    padding: 0;
    margin: 0;
}
body {
    padding-bottom:10px;
    padding-top: 0px;
    padding-left: 10px;
    text-align: left;
    font-family: Arial, sans-serif, Helvetica, Tahoma;
    font-size:13px;
    line-height: 1.5em;
    color:#4d4d4d;
}
a:hover { text-decoration:underline; }
p{
    line-height:18px;
    padding: 5px 0;
}  
</style>
</head>
<body>
  <p align=center><a href="javascript:history.go(-1)">返回</a></p>
<?php

if(empty($data)) {
  echo "无法解析该excel文档.请确认该excel文档格式正确!";
  exit();
}

if(empty($data->sheets[0])) {
  echo "无法解析该excel文档.请确认该excel文档格式正确!";  
  exit();
}


$numRows = $data->sheets[0]['numRows'];
if(0 >= $numRows) {
  echo "无法解析该excel文档.请确认该excel文档格式正确!";  
  exit();
}
$numCols = $data->sheets[0]['numCols'];

$curRow = 1;

$sheet0 = $data->sheets[0]['cells'];

$addCount = 0;
$updateCount = 0;

$db = new DBMySQL();

// 循环读取单词
while(true) {
  
  // 开始读取1个单词
  $word = trim($sheet0[$curRow++][1]);
  if(empty($word)) {
  	break;
  }
  
  if($curRow > $numRows) {
    echo "格式有错误!<br>";
    break;
  }
  
  // 读取音标
  $phonetic = trim($sheet0[$curRow++][1]);
  
  // 读取该单词的各个释义  
  $meaningCn = "";
  $meaningCns = array();
  $meaningEns = array();
  $examples = array();
  $synonyms = array(); 
  $antonyms = array();    
  
  $firstMeaning = true;
  $wrong = false;
  while(true) {
    
    // 中文释义
    $meaningCn = trim($sheet0[$curRow][1]);
    if(empty($meaningCn)) {
      if($firstMeaning) {
        $wrong = true;
        echo "格式有错误!<br>";        
      }
      break;
    }
    $curRow ++;
    $meaningCns[] = $meaningCn;
    $firstMeaning = false;
    
    // 英文释义
    $meaningEn = trim($sheet0[$curRow++][1]);
    $meaningEns[] = $meaningEn;
    
    // 例句
    $example = trim($sheet0[$curRow++][1]);
    $examples[] = $example;
    
    // 近义词    
    $tmpArray = array();
    for($j=1; $j<=$numCols; $j++) {
      $tmpStr = trim($sheet0[$curRow][$j]);
      if(empty($tmpStr)) {
        break;
      }
      $tmpArray[] = $tmpStr;
    }
    $synonyms[] = $tmpArray;
    $curRow ++;
    
    // 反义词    
    $tmpArray = array();
    for($j=1; $j<=$numCols; $j++) {
      $tmpStr = trim($sheet0[$curRow][$j]);
      if(empty($tmpStr)) {
        break;
      }
      $tmpArray[] = $tmpStr;
    }
    $antonyms[] = $tmpArray;
    $curRow ++;
    
    // 到达最后一行
    if($numRows < $curRow) {
      break;
    }
  }  
  
  // 格式有错误,不再继续入库
  if($wrong) { 
    break;
  }
  
  // 入库操作
		
	// 先查库里是否有该单词
	$word = str_replace('\'', '\'\'', $word);
	$rs = $db->Query("select word_id from main_word where content = '$word' and category = $t limit 1");
	$slug = empty($phonetic) ? '' : $phonetic;
	$slug = str_replace('\'', '\'\'', $slug);
	$nMeaningCount = count($meaningCns);
	$currentTime = time();
	$wordId = 0;
	if(empty($rs)) { // 库里没有,则添加该单词信息	  	  
		
		// 插入main_word表
		$wordIndex = strtoupper($word[0]);
		$wordId = $db->Insert("insert into main_word(content,word_index,slug,create_time,category) values('$word','$wordIndex','$slug','$currentTime','$t')");						
		
		$addCount ++;
	} else { // 修改单词信息
		// 更新音标
		$wordId = $rs[0]['word_id'];		
		$db->Modify("update main_word set slug='$slug' where word_id = $wordId");
		
		// 先删除该单词的所有信息
		$db->Modify("DELETE wm,  m,  ms,  s,  msy,  sy,  ma,  a FROM word_meaning wm,  meaning m,  meaning_sentence ms,  sentence s,  meaning_synonym msy,  synonym sy,  meaning_antonym ma,  antonym a WHERE wm.word_id = $wordId    AND m.meaning_id = wm.meaning_id    AND ms.meaning_id = wm.meaning_id    AND s.sentence_id = ms.sentence_id    AND msy.meaning_id = wm.meaning_id    AND sy.synonym_id = msy.synonym_id    AND ma.meaning_id = wm.meaning_id    AND a.antonym_id = ma.antonym_id");
		
		
		/*
		// 更新含义
		// 先查出库里该单词的所有含义
	  $rsMeaning = $db->Query("select m.meaning_id,m.cn_translate from word_meaning wm, meaning m WHERE wm.meaning_id = m.meaning_id and wm.word_id=$wordId");
	  $nExistMeaningCount = count($rsMeaning);
	  $existMeanings = array();
	  $existStates = array();
	  for($i=0; $i<$nExistMeaningCount; $i++) { // 遍历库里的含义
	    $val = $rsMeaning[$i];
	    $cnTran = $val['cn_translate'];
	    $existMeanings[$cnTran] = $val['meaning_id'];
	    $existStates[$cnTran] = 0;
	  }
    for($i=0; $i<$nMeaningCount; $i++) { // 遍历excel的含义
      $val = $meaningCns[$i];
      $existMeaningId = $existMeanings[$val];
      if(empty($existMeaningId)) { // 库里不存在该含义,则插入

  		  // 中英文含义
  		  $meaningId = $db->Insert("INSERT INTO meaning(cn_translate,eng_translate) VALUES('$meaningCns[$i]', '$meaningEns[$i]')");
  			$db->Insert("INSERT INTO word_meaning(word_id, meaning_id) VALUES($wordId,$meaningId)");
  			
  			// 插入例句
  			$sentenceId = $db->Insert("INSERT INTO sentence(content) VALUES('$examples[$i]')");
  			$db->Insert("INSERT INTO meaning_sentence(meaning_id, sentence_id) VALUES($meaningId,$sentenceId)");
  			
  			// 插入同义词
  			$nTotal = count($synonyms[$i]);
  			for($j=0; $j<$nTotal; $j++) {
  			  $tmpStr = $synonyms[$i][$j];
    			$synonymId = $db->Insert("INSERT INTO synonym(content) VALUES('$tmpStr')");
    			$db->Insert("INSERT INTO meaning_synonym(meaning_id, synonym_id) VALUES($meaningId,$synonymId)");
  			}
  			
  			// 插入反义词
  			$nTotal = count($antonyms[$i]);
  			for($j=0; $j<$nTotal; $j++) {
  			  $tmpStr = $antonyms[$i][$j];
    			$antonymId = $db->Insert("INSERT INTO antonym(content) VALUES('$tmpStr')");
    			$db->Insert("INSERT INTO meaning_antonym(meaning_id, antonym_id) VALUES($meaningId,$antonymId)");
  			}	        
        
      } else { // 库里存在该含义,则更新
        
        // 更新英文含义
        $db->Modify("UPDATE meaning SET eng_translate='$meaningEns[$i]' WHERE meaning_id=$existMeaningId");
        
        // 更新例句
        $db->Modify("UPDATE sentence s, meaning_sentence ms SET s.content='$examples[$i]' WHERE ms.meaning_id=$existMeaningId and ms.sentence_id = s.sentence_id");
        
        // 更新同义词
    		// 先查出库里该含义的所有同义词
    	  $rsSynonym = $db->Query("select ms.synonym_id,s.content from meaning_synonym ms, synonym s WHERE ms.synonym_id = s.synonym_id and ms.meaning_id=$existMeaningId and s.content<>''");
    	  $nExistSynonymCount = count($rsSynonym);
    	  $existSynonyms = array();
    	  $existSynonymStates = array();
    	  for($j=0; $j<$nExistSynonymCount; $j++) { // 遍历库里的同义词
    	    $valSynonym = $rsSynonym[$j];
    	    $content = $valSynonym['content'];
    	    $existSynonyms[$content] = $valSynonym['synonym_id'];
    	    $existSynonymStates[$content] = 0;
    	  }    	  
    	  $nTotal = count($synonyms[$i]);
        for($j=0; $j<$nTotal; $j++) { // 遍历excel的同义词
          $valSynonym = $synonyms[$i][$j];
          $existSynonymId = $existSynonyms[$valSynonym];
          if(empty($existSynonymId)) { // 库里不存在该同义词,则插入   
       			$synonymId = $db->Insert("INSERT INTO synonym(content) VALUES('$valSynonym')");
       			$db->Insert("INSERT INTO meaning_synonym(meaning_id, synonym_id) VALUES($existMeaningId,$synonymId)");            
          } else {
            $existSynonymStates[$valSynonym] = 1;
          }
    	  }    	  
    	  // 删除库里有的但excel没有的同义词
    	  foreach ($existSynonymStates as $key => $state) {
    	    if(0 == $state) { // 删除该同义词
    	      $db->Modify("delete msy,sy from meaning_synonym msy,synonym sy where msy.meaning_id = $existMeaningId and msy.synonym_id=$existSynonyms[$key] and sy.synonym_id = msy.synonym_id");
    	    }
    	  }	    	  
    	  
        // 更新反义词
    		// 先查出库里该含义的所有反义词
    	  $rsAntonym = $db->Query("select ma.antonym_id,a.content from meaning_antonym ma, antonym a WHERE ma.antonym_id = a.antonym_id and ma.meaning_id=$existMeaningId and a.content<>''");
    	  $nExistAntonymCount = count($rsAntonym);
    	  $existAntonyms = array();
    	  $existAntonymStates = array();
    	  for($j=0; $j<$nExistAntonymCount; $j++) { // 遍历库里的反义词
    	    $valAntonym = $rsAntonym[$j];
    	    $content = $valAntonym['content'];
    	    $existAntonyms[$content] = $valAntonym['antonym_id'];
    	    $existAntonymStates[$content] = 0;
    	  }    	  
    	  $nTotal = count($antonyms[$i]);
        for($j=0; $j<$nTotal; $j++) { // 遍历excel的反义词
          $valAntonym = $antonyms[$i][$j];
          $existAntonymId = $existAntonyms[$valAntonym];
          if(empty($existAntonymId)) { // 库里不存在该反义词,则插入   
       			$antonymId = $db->Insert("INSERT INTO antonym(content) VALUES('$valAntonym')");
       			$db->Insert("INSERT INTO meaning_antonym(meaning_id, antonym_id) VALUES($existMeaningId,$antonymId)");            
          } else {
            $existAntonymStates[$valAntonym] = 1;
          }
    	  }    	  
    	  // 删除库里有的但excel没有的反义词
    	  foreach ($existAntonymStates as $key => $state) {
    	    if(0 == $state) { // 删除该反义词
    	      $db->Modify("delete ma,a from meaning_antonym ma,antonym a where ma.meaning_id = $existMeaningId and ma.synonym_id=$existAntonyms[$key] and a.synonym_id = ma.synonym_id");
    	    }
    	  }	    	      	  
        
        $existStates[$val] = 1;
      }	                
    }
	  
	  // 删除库里有的但excel没有的含义
	  foreach ($existStates as $key => $state) {
	    if(0 == $state) { // 删除该含义,及其例句,同义词,反义词记录
	      $mId = $existMeanings[$key];
	      $db->Modify("delete wm,m,ms,s,msy,sy,ma,a from word_meaning wm, meaning m,meaning_sentence ms,sentence s,meaning_synonym msy,synonym sy,meaning_antonym ma,antonym a where wm.word_id = $wordId and wm.meaning_id = $mId and m.meaning_id = $mId and ms.meaning_id = $mId and s.sentence_id = ms.sentence_id and msy.meaning_id = $mId and sy.synonym_id = msy.synonym_id and ma.meaning_id = $mId and a.antonym_id = ma.antonym_id");
	    }
	  }	  
	  */	  		
				
		$updateCount ++;
	}
	
	// 插入释义		
	for($i=0; $i<$nMeaningCount; $i++) {		  
	  
	  // 中英文含义
	  $meaningCns[$i] = str_replace('\'', '\'\'', $meaningCns[$i]);
	  $meaningEns[$i] = str_replace('\'', '\'\'', $meaningEns[$i]);
	  $meaningId = $db->Insert("INSERT INTO meaning(cn_translate,eng_translate) VALUES('$meaningCns[$i]', '$meaningEns[$i]')");
		$db->Insert("INSERT INTO word_meaning(word_id, meaning_id) VALUES($wordId,$meaningId)");
		
		// 插入例句
		$examples[$i] = str_replace('\'', '\'\'', $examples[$i]);
		$sentenceId = $db->Insert("INSERT INTO sentence(content) VALUES('$examples[$i]')");
		$db->Insert("INSERT INTO meaning_sentence(meaning_id, sentence_id) VALUES($meaningId,$sentenceId)");
		
		// 插入同义词			
		$nTotal = count($synonyms[$i]);
		for($j=0; $j<$nTotal; $j++) {	  
  	  $tmpStr = $synonyms[$i][$j];
  	  $tmpStr = str_replace('\'', '\'\'', $tmpStr);
			$synonymId = $db->Insert("INSERT INTO synonym(content) VALUES('$tmpStr')");
			$db->Insert("INSERT INTO meaning_synonym(meaning_id, synonym_id) VALUES($meaningId,$synonymId)");
		}
		
		// 插入反义词
		$nTotal = count($antonyms[$i]);
		for($j=0; $j<$nTotal; $j++) {
		  $tmpStr = $antonyms[$i][$j];
		  $tmpStr = str_replace('\'', '\'\'', $tmpStr);
			$antonymId = $db->Insert("INSERT INTO antonym(content) VALUES('$tmpStr')");
			$db->Insert("INSERT INTO meaning_antonym(meaning_id, antonym_id) VALUES($meaningId,$antonymId)");
		}			
		
	}	
	
  
  $curRow ++;
  // 到达最后一行
  if($numRows < $curRow) {
    break;
  }  
}  
   
?>

<p align=center>共添加<?=$addCount?>个单词, 共更新<?=$updateCount?>个单词.</p>

</body>
</html>